﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using TwitterDotNet;
using TwitterDotNet.Core;

using System.Data;
using System.Data.Common;
using System.Data.SQLite;

namespace TwitterDotNet.SQLite
{
    public class API
    {
        public static void InsertUser(string filename, string tablename, User user)
        {
            string selectstring = string.Format("SELECT Id, Name, ScreenName, Location, Description, ProfileImageUrl, Url, Protected FROM {0}", tablename);
            string insertstring = string.Format("INSERT INTO {0} (Id, Name, ScreenName, Location, Description, ProfileImageUrl, Url, Protected) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)", tablename);
            IDbConnection connection = new SQLiteConnection(string.Format("Data Source={0}", filename));
            connection.Open();

            IDbTransaction transaction = connection.BeginTransaction();
            SQLiteDataAdapter da = new SQLiteDataAdapter();

            IDbCommand selectcommand = connection.CreateCommand();
            selectcommand.CommandText = selectstring;
            IDbCommand insertcommand = connection.CreateCommand();
            insertcommand.CommandText = insertstring;

            DataSet ds = new DataSet();

            da.SelectCommand = selectcommand as SQLiteCommand;
            da.Fill(ds, tablename);

            DataRow row = ds.Tables[tablename].NewRow();
            row["Id"] = user.Id;
            row["Name"] = user.Name;
            row["ScreenName"] = user.ScreenName;
            row["Location"] = user.Location;
            row["Description"] = user.Description;
            row["ProfileImageUrl"] = user.ProfileIamgeUrl.ToString();
            row["Url"] = user.Url == null ? "" : user.Url.ToString();
            row["Protected"] = user.Protected;

            ds.Tables[tablename].Rows.Add(row);

            DbParameter p1 = insertcommand.CreateParameter() as SQLiteParameter;
            p1.ParameterName = "@p1";
            p1.SourceColumn = "Id";
            insertcommand.Parameters.Add(p1);

            DbParameter p2 = insertcommand.CreateParameter() as SQLiteParameter;
            p2.ParameterName = "@p2";
            p2.SourceColumn = "Name";
            insertcommand.Parameters.Add(p2);

            DbParameter p3 = insertcommand.CreateParameter() as SQLiteParameter;
            p3.ParameterName = "@p3";
            p3.SourceColumn = "ScreenName";
            insertcommand.Parameters.Add(p3);

            DbParameter p4 = insertcommand.CreateParameter() as SQLiteParameter;
            p4.ParameterName = "@p4";
            p4.SourceColumn = "Location";
            insertcommand.Parameters.Add(p4);

            DbParameter p5 = insertcommand.CreateParameter() as SQLiteParameter;
            p5.ParameterName = "@p5";
            p5.SourceColumn = "Description";
            insertcommand.Parameters.Add(p5);

            DbParameter p6 = insertcommand.CreateParameter() as SQLiteParameter;
            p6.ParameterName = "@p6";
            p6.SourceColumn = "ProfileImageUrl";
            insertcommand.Parameters.Add(p6);

            DbParameter p7 = insertcommand.CreateParameter() as SQLiteParameter;
            p7.ParameterName = "@p7";
            p7.SourceColumn = "Url";
            insertcommand.Parameters.Add(p7);

            DbParameter p8 = insertcommand.CreateParameter() as SQLiteParameter;
            p8.ParameterName = "@p8";
            p8.SourceColumn = "Protected";
            insertcommand.Parameters.Add(p8);

            insertcommand.Transaction = transaction;
            da.InsertCommand = insertcommand as SQLiteCommand;
            try
            {
                da.Update(ds, tablename);
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
            }
            finally
            {
                transaction.Dispose();
            }
        }

        public static User[] SelectUser(string filename, string tablename, string condition)
        {
            string selectstring = string.Format("SELECT * FROM {0} {1}", tablename, condition);

            IDbConnection connection = new SQLiteConnection(string.Format("Data Source={0}", filename));
            connection.Open();

            IDbTransaction transaction = connection.BeginTransaction();
            SQLiteDataAdapter da = new SQLiteDataAdapter();

            IDbCommand selectcommand = connection.CreateCommand();
            selectcommand.CommandText = selectstring;

            DataSet ds = new DataSet();

            da.SelectCommand = selectcommand as SQLiteCommand;
            da.Fill(ds, tablename);

            List<User> userlist = new List<User>();
            foreach (DataRow row in ds.Tables[tablename].Rows)
            {
                userlist.Add(User.CreateUser(long.Parse(row["Id"].ToString()), row["Name"].ToString(), row["ScreenName"].ToString(), row["Description"].ToString(), new Uri(row["ProfileImageUrl"].ToString()), row["Url"].ToString(), row["Protected"].ToString() == "0" ? false : true));
            }
            transaction.Dispose();
            return userlist.ToArray();
        }

        public static void InsertTweet(string filename, string tablename, Tweet tweet)
        {
            string selectstring = string.Format("SELECT Id, CreatedAt, Text, Source, UserId FROM {0}", tablename);
            string insertstring = string.Format("INSERT INTO {0} (Id, CreatedAt, Text, Source, UserId) VALUES (@p1, @p2, @p3, @p4, @p5)", tablename);
            IDbConnection connection = new SQLiteConnection(string.Format("Data Source={0}", filename));
            connection.Open();

            IDbTransaction transaction = connection.BeginTransaction();
            SQLiteDataAdapter da = new SQLiteDataAdapter();

            IDbCommand selectcommand = connection.CreateCommand();
            selectcommand.CommandText = selectstring;
            IDbCommand insertcommand = connection.CreateCommand();
            insertcommand.CommandText = insertstring;

            DataSet ds = new DataSet();

            da.SelectCommand = selectcommand as SQLiteCommand;
            da.Fill(ds, tablename);

            DataRow row = ds.Tables[tablename].NewRow();
            row["Id"] = tweet.Id;
            row["CreatedAt"] = tweet.CreatedAt;
            row["Text"] = tweet.Text;
            row["Source"] = tweet.Source;
            row["UserId"] = tweet.User.Id;

            ds.Tables[tablename].Rows.Add(row);

            DbParameter p1 = insertcommand.CreateParameter() as SQLiteParameter;
            p1.ParameterName = "@p1";
            p1.SourceColumn = "Id";
            insertcommand.Parameters.Add(p1);

            DbParameter p2 = insertcommand.CreateParameter() as SQLiteParameter;
            p2.ParameterName = "@p2";
            p2.SourceColumn = "CreatedAt";
            insertcommand.Parameters.Add(p2);

            DbParameter p3 = insertcommand.CreateParameter() as SQLiteParameter;
            p3.ParameterName = "@p3";
            p3.SourceColumn = "Text";
            insertcommand.Parameters.Add(p3);

            DbParameter p4 = insertcommand.CreateParameter() as SQLiteParameter;
            p4.ParameterName = "@p4";
            p4.SourceColumn = "Source";
            insertcommand.Parameters.Add(p4);

            DbParameter p5 = insertcommand.CreateParameter() as SQLiteParameter;
            p5.ParameterName = "@p5";
            p5.SourceColumn = "UserId";
            insertcommand.Parameters.Add(p5);

            insertcommand.Transaction = transaction;
            da.InsertCommand = insertcommand as SQLiteCommand;
            try
            {
                da.Update(ds, tablename);
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
            }
            finally
            {
                transaction.Dispose();
            }
        }

        public static Tweet[] SelectTweet(string filename, string tablename, string condition)
        {
            string selectstring = string.Format("SELECT * FROM {0} {1}", tablename, condition);

            IDbConnection connection = new SQLiteConnection(string.Format("Data Source={0}", filename));
            connection.Open();

            IDbTransaction transaction = connection.BeginTransaction();
            SQLiteDataAdapter da = new SQLiteDataAdapter();

            IDbCommand selectcommand = connection.CreateCommand();
            selectcommand.CommandText = selectstring;

            DataSet ds = new DataSet();

            da.SelectCommand = selectcommand as SQLiteCommand;
            da.Fill(ds, tablename);

            List<Tweet> tweetlist = new List<Tweet>();
            foreach (DataRow row in ds.Tables[tablename].Rows)
            {
                tweetlist.Add(Tweet.CreateStatus(long.Parse(row["Id"].ToString()), row["CreatedAt"].ToString(), row["Text"].ToString(), row["Source"].ToString(), long.Parse(row["UserId"].ToString())));
            }
            transaction.Dispose();
            return tweetlist.ToArray();
        }

    }
}
